package com.app.diary.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

/**
 * 数据库操作工具
 */
public class DbHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "diary.db"; // 数据库的名称
    public static final int DB_VERSION = 4; // 数据库的版本号
    public static final String TABLE_DIARY = "diary"; // 日记表
    public static final String TABLE_USERS = "users"; // 用户表

    public DbHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createDiaryTable(db);
        createUsersTable(db); // 创建用户表
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            createUsersTable(db); // 如果需要创建用户表
        }
        if (oldVersion < 3) {
            // 升级到版本3：为 users 表添加 email 列
            db.execSQL("ALTER TABLE " + TABLE_USERS + " ADD COLUMN email TEXT DEFAULT '';");
            Log.d("DbHelper", "Email column added to users table during upgrade.");
        }
        if (oldVersion < 4) {
            // 升级到版本4：为 diary 表添加 user_id 列
            db.execSQL("ALTER TABLE " + TABLE_DIARY + " ADD COLUMN user_id INTEGER NOT NULL DEFAULT 0;");
            Log.d("DbHelper", "User ID column added to diary table during upgrade.");
        }
    }

    /**
     * 创建日记表
     */
    private void createDiaryTable(SQLiteDatabase db) {
        // 如果表已存在则删除表（谨慎使用）
        String dropSql = "DROP TABLE IF EXISTS " + TABLE_DIARY + ";";
        db.execSQL(dropSql);

        // 创建表
        String createSql = "CREATE TABLE IF NOT EXISTS " + TABLE_DIARY + " ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                + "date INTEGER NOT NULL, "
                + "weather VARCHAR NOT NULL, "
                + "title VARCHAR NOT NULL, "
                + "content VARCHAR NOT NULL, "
                + "create_time INTEGER NOT NULL, "
                + "update_time INTEGER NOT NULL, "
                + "user_id INTEGER NOT NULL" // 添加 user_id 列
                + ");";
        db.execSQL(createSql);
    }

    /**
     * 创建用户表
     */
    private void createUsersTable(SQLiteDatabase db) {
        // 如果表已存在则删除表（谨慎使用）
        String dropSql = "DROP TABLE IF EXISTS " + TABLE_USERS + ";";
        db.execSQL(dropSql);

        // 创建表
        String createSql = "CREATE TABLE IF NOT EXISTS " + TABLE_USERS + " ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                + "username TEXT NOT NULL UNIQUE, " // 确保用户名唯一
                + "email TEXT NOT NULL UNIQUE, " // 添加 email 字段，并确保唯一性
                + "password TEXT NOT NULL" // 密码字段
                + ");";
        db.execSQL(createSql);
    }
}